package net.wantongtech.xa.uploadbasicinfo.service;

import lombok.extern.slf4j.Slf4j;
import net.wantongtech.xa.uploadbasicinfo.dao.GeneratePortalTransOutViewScriptDao;
import org.apache.commons.io.FileUtils;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 生成TR_PORTAL_TRANSOUT视图
 *
 * @author MaShibo
 * 2021/3/12 0012
 */
@Slf4j
@Service
public class GeneratePortalTransOutViewScriptService {

    private Pattern pattern = Pattern.compile("^TR_PORTAL_TRANSOUT_(?<month>\\d{6})$");

    @Resource
    private GeneratePortalTransOutViewScriptDao generatePortalTransOutViewScriptDao;

    public void generate(String targetPath) {
        List<String> tableNames = generatePortalTransOutViewScriptDao.queryAllPortalTransOutTableNames();
        int size = tableNames.size();
        log.info("共查询到{}个表", size);

        StringBuilder builder = new StringBuilder();
        builder.append("create or replace view tr_portal_transout\n as\n");

        LocalDate startDate = null;
        LocalDate endDate = null;

        DateTimeFormatter monthFormatter = DateTimeFormat.forPattern("yyyyMMdd");
        for (int i = 0; i < size; i++) {
            String tableName = tableNames.get(i);
            log.info("表名：{}", tableName);
            String alias = String.format("t%d", i + 1);
            builder.append(String.format("SELECT %s.*,'%s' as tableName%n", alias, tableName))
                    .append(String.format("  FROM %s %s%n", tableName, alias));

            Matcher matcher = pattern.matcher(tableName.toUpperCase());
            if (matcher.matches()) {
                String month = matcher.group("month");
                startDate = LocalDate.parse(month + "01", monthFormatter);
                endDate = startDate.plusMonths(1);

                builder.append(String.format("  WHERE %s.transtime >= DATE '%s'%n", alias, startDate.toString()))
                        .append(String.format("  AND %s.transtime < DATE '%s'%n", alias, endDate.toString()));
            } else if (startDate != null) {
                builder.append(String.format("  WHERE %s.transtime >= DATE '%s'%n", alias, endDate.toString()));
            }

            if (i < size - 1) {
                builder.append(" UNION ALL\n");
            }

        }

        builder.append(" WITH READ ONLY; ");

        log.info("生成脚本文件到：{}", targetPath);
        try {
            FileUtils.writeStringToFile(new File(targetPath), builder.toString(), StandardCharsets.UTF_8.displayName());
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

}
